<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * 2013-4-13 by sutroon created v1
 * 2013-9-3 by sutroon v2
 */
/**
 * 网页实用方法类
 * 
 * 提供网页表单实用功能，如获取安全参数等
 * Version 1.0 2013-4-13 sutroon Create 
 * Version 2.0 2013-9-3 sutroon Refactoring
 * 
 * @author sutroon <14507247@qq.com>
 * @license http://www.sochishun.com
 * @since Version 1.0 2013-4-13
 * @version $Id: SoWebUtility.php v 1.0 2013-09-3 06:55:42Z sutroon Exp $
 * @package Core
 */
class SoDbMysql5 implements ISoDbHelper{
    
    /**
     * 获取或设置数据库连接资源
     * @var resource 
     * @access public
     */
    public $link;
    
    /**
     * 获取或设置数据库连接信息
     * @var array 
     * @access public
     */
    public $dbinfo;
    
    /**
     * 打开数据库连接
     */
    function __construct($dbinfo) {
        $this->dbinfo=$dbinfo;
        $this->link = mysql_connect($dbinfo['DB_HOST'], $dbinfo['DB_USER'], $dbinfo['DB_PWD']) or die('Connect fail:' . mysql_error());
        if ($dbinfo['DB_NAME'])  mysql_select_db($dbinfo['DB_NAME']) or die('database[' . $dbinfo['DB_NAME'] . '] not found!' . mysql_error());
        mysql_query('set names utf8');
    }
    
    /**
     * 析构方法
     * 
     * 注销数据库连接资源
     */
    function __destruct() {
        if (isset($this->link))
            unset($this->link);
            //mysql_close($this->link);
    }

    public function combin_select_sql($table, $search, $fields = '*') {
        if ($search){    
            $p= strripos(trim($search),'limit ');            
            if($p===false || $p>0)  $search = ' where ' . $search;
        }
        return 'select ' . $fields . ' from ' . $table .' '. $search;
    }
    
    /**
     * 查询并返回数组格式的数据集,如果没有数据则返回null
     * @param type $table
     * @param type $search
     * @param type $fields
     * @return type
     * @since 1.1 2014-3-7 by sutroon 新增MYSQL_ASSOC参数,过滤数字数组
     */
    function select_arrayset($table, $search, $fields = '*') {
        $sql =$this->combin_select_sql($table, $search, $fields);
        $res = mysql_query($sql) or die('query failed:' . mysql_error());
        while ($row = mysql_fetch_array($res,MYSQL_ASSOC )) {
            $return[] = $row;
        }
        mysql_free_result($res);
        return isset($return) ? $return : null;
    }
    
    /**
     * 查询分页数据集,返回数组格式数据集,没有数据则返回null
     * @param type $pageindex
     * @param type $pagesize
     * @param type $recordcount
     * @param type $from
     * @param string $orderby
     * @param type $fields
     * @return type
     * @since 1.0.0 2013-9-30 sutroon
     */
    function select_pagingset($pageindex,$pagesize,&$recordcount,$from,$orderby='CreatedTime desc',$fields='*'){
		if(!$from || !$fields) exit('setquery参数有误');
		if($orderby) $orderby=' order by '.$orderby;
		$recordcount=$this->select_value($from,'','count(0)');                
                $sql=$from.$orderby.' limit '.(($pageindex-1) * $pagesize).','.$pagesize;
                return $this->select_arrayset($sql, '',$fields);
	}
        
        /**
         * 查询某列的值的集合,没有数据则返回null
         * @param type $table
         * @param type $search
         * @param type $fields
         * @param type $columnid
         * @return type
         * @since 1.0.0 2013-9-30 sutroon
         */
    function select_column_values($table, $search,$fields='*', $columnid=0) {
        $sql = $this->combin_select_sql($table, $search, $fields);
        $res = mysql_query($sql) or die('query failed:' . mysql_error());
        while ($row = mysql_fetch_array($res)) {
            $return[] = $row[$columnid];
        }
        mysql_free_result($res);
        return $return;
    }

    /**
     * 
     * @param type $table
     * @param type $search
     * @param type $field
     * @return type
     */
    public function select_object($table, $search, $field) {
        $sql = combin_select_sql($table, $search, $field);
        $res = mysql_query($sql) or die('query failed:' . mysql_error());
        $return = mysql_fetch_object($res);
        mysql_free_result($res);
        return $return;
    }

    /**
     * 选择单元格值,无数据则返回默认值
     * @param type $table
     * @param type $search
     * @param type $fields
     * @param type $defv
     * @return type
     */
    public function select_value($table, $search, $fields = '*', $defv = '') {
        $sql = $this->combin_select_sql($table, $search, $fields);
        $res = mysql_query($sql) or die('query failed:' . mysql_error());
        while ($row = mysql_fetch_array($res)) {
            $return = $row;
            break;
        }
        mysql_free_result($res);
        return isset($return) && $return[0] ? $return[0] : $defv;
    }

    /**
     * 选择一行,返回数组集,无数据则返回null
     * @param type $table
     * @param type $search
     * @param type $fields
     * @return type
     */
    public function select_one_row($table, $search, $fields = '*') {
        $sql = $this->combin_select_sql($table, $search, $fields);
        $res = mysql_query($sql) or die('query failed:' . mysql_error());
        while ($row = mysql_fetch_array($res)) {
            $return = $row;
            break;
        }
        mysql_free_result($res);
        return isset($return) ? $return : null;
    }
    
    /**
     * 执行sql命令
     * @param type $sql
     * @return type
     */
	public function execute_sql($sql){
		return mysql_query($sql) or die('query failed:'.mysql_error());
	}
	
        /**
         * 更新数据
         * @param type $table
         * @param type $fields
         * @param type $search
         * @return string
         */
	public function update_records($table,$fields,$search){
		if(!$table || !$fields || !$search) return '';
		return mysql_query("update $table set $fields where $search") or die('query failed:'.mysql_error());
	}
	
        /**
         * 新增记录
         * @param type $table
         * @param type $names
         * @param type $values
         * @param type $lastsearch
         * @param type $key
         * @return string
         * @since 1.0.0 2013-5-23 sutroon
         */
	public function insert_record($table,$names,$values,$lastsearch='order by ID desc',$key='ID'){
		if(!$table || !$names || !$values) return '';
		mysql_query("insert into $table ($names) values ($values)") or die('query failed:'.mysql_error());
                if(!$lastsearch) return 'SUCCESS';
                $res = mysql_query(sprintf('select %s from %s %s',$key,$table,$lastsearch)) or die('query failed:' . mysql_error());
                while ($row = mysql_fetch_array($res)) {
                    $return = $row;
                    break;
                }
                mysql_free_result($res);
                return isset($return) ? $return[0] : 'SUCCESS';                
	}
	
        /**
         * 删除记录 
         * @param type $table
         * @param type $search
         * @return string
         * @since 1.0.0 2013-5-23 sutroon
         */
	public function delete_records($table,$search){
		if(!$table || !$search) return '';
		return mysql_query("delete from $table where $search") or die('query failed:'.mysql_error());
	}
        
        /**
         * 获取包含所有表的数组
         * @return array
         * @since 1.0.0 2012-12-16
         */
        function show_tables(){            
            $rs = mysql_query('show tables;');
            if(!$rs) return '没有任何表';
            $arr=array();
            while($row = mysql_fetch_row($rs))
            {
                $arr[]=$row[0];
           }
            mysql_free_result($rs);
           return $arr;   
        }
	
        /**
         * 获取数据表的创建语句的备份数据
         * @param type $table
         * @param type $isdrop
         * @param type $nl
         * @return string
         * @since 1.0.0 2013-5-20 network
         */
	function backup_table_structure($table,$isdrop=false,$nl='\r\n')	{
		$schema_create='';
		if($isdrop){ $schema_create.= "DROP TABLE IF EXISTS `$table`;$nl";}
		$result   =mysql_query("SHOW CREATE TABLE $table");
		$row=mysql_fetch_array($result);
		//$schema_create   .= $nl.'-- '.$row[0].$nl;
		$schema_create   .= $row[1].$nl;
		return $schema_create;
	}

        /**
         * 获得数据表内容的备份数据
         * @param type $table
         * @param type $crlf
         * @return string
         * @since 1.0.0 2013-5-20 network
         */
	function backup_table_data($table, $crlf='\r\n')
	{
		$schema_create = '';
		$temp = '';
		$result = mysql_query("SELECT * FROM $table");
		$i = 0;
		while($row = mysql_fetch_row($result))
		{
			  $schema_insert = "INSERT INTO `$table` VALUES (";
			  for($j = 0; $j < mysql_num_fields($result); $j++)
			  {
				  if(!isset($row[$j]))
					$schema_insert.= ' NULL,';
				  elseif($row[$j] != '')
					$schema_insert .= " '".addslashes($row[$j])."',";
				  else
					$schema_insert .= " '',";
			  }
			  $schema_insert = preg_replace("/,$/", "",$schema_insert);
			  $schema_insert .= ");$crlf";
			  $temp = $temp . $schema_insert;
			  $i++;
		}
                mysql_free_result($result);
		return $temp;
	}
        
        /**
         * 备份整个数据库
         * 
         * @since 1.0.0 2013-5-18 sutroon
         */
        public function backup_all_db(){
            $rs = mysql_query('show tables;');
            if(!$rs) return '没有任何表';

            // 避免数据量过大，导出不全的情况出现。
            ini_set("max_execution_time", "180");

            // 换行控制符
            $crlf="\r\n";
            //$crlf=chr(13);
            $content_schema='';
            $content_data='';
            $rsschema=null;
            while($row = mysql_fetch_row($rs))
            {
                $rsschema=mysql_query('describe '.$row[0]);
                while($rowschema = mysql_fetch_row($rsschema)){
                    foreach($rowschema as $keyschema=>$valschema){
                        if(is_numeric($keyschema)) continue;
                        $content_schema.=' '.$keyschema.'="'.$valschema.'"';
                    }
                }
                $content_schema.=$this->backup_table_structure($row[0],false,$crlf).';'.$crlf.$crlf;
                $content_data.=$this->backup_table_data($row[0]);
           }
            mysql_free_result($rs);
           return $content_schema.$content_data;   
        }
        
        /**
         * 获取分页查询语句
         * @param type $pageid
         * @param type $pagesize
         * @return type
         * @example echo $pager->get_pagingsql('select * from tusermember where sex=\'%s\' and point > %u','F',100);
         * @since 1.0.0 2013-5-21 sutroon
         */
	public function get_paging_sql($pageid,$pagesize){
		return call_user_func_array('sprintf',func_get_args()).' limit '.(($pageid-1)*$pagesize).','.$pagesize;
	}
}
?>
